﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetAllClassToAccess]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetAllClassToAccess];
GO
CREATE PROCEDURE [dbo].[sproc_GetAllClassToAccess]
    @UserName nvarchar(300),
    @RightCode INT,
    @InHerit bit=0
AS
SET NOCOUNT ON

DECLARE @Staff_id INT
DECLARE @Position_id INT
DECLARE @ids nvarchar(2000)
DECLARE @id INT
DECLARE @oldids nvarchar(2000)
DECLARE @sql nVARCHAR(2000)

SET @staff_id=0

--取得用户的ID和职位ID
SELECT
    @staff_id = a.staff_id,
    @Position_id = b.Position_ID 
FROM 
    uds_staff a,
    uds_staff_in_position b 
WHERE
    a.staff_id = b.staff_id 
    and staff_name = @username

--创建用户组
declare @staff table (staff_id INT);

IF @STAFF_ID>0
BEGIN
    
    --插入自己（将来可能会插入职位下级)
    INSERT INTO @staff (staff_id) VALUES(@staff_id)    


    --得到用户名有权的CLASSID表
    declare @class table (ClassID INT);

    --从职位中，能查看的接点
    INSERT INTO @class 
        SELECT team_id 
            FROM 
                UDS_Assign_Rule a
            WHERE a.Position_id in (
                            SELECT Position_id 
                                FROM 
                                    uds_staff_in_position 
                                WHERE staff_id IN (
                                            SELECT staff_id 
                                                FROM @staff
                                           )
                        ) 
                  and based_on =1 
                  and a.act_id=@RIGHTCode
    --从项目中，能查看的接点
    INSERT INTO @class 
        SELECT team_id 
            FROM 
                UDS_Assign_Rule a
            WHERE a.team_id in (
                        SELECT team_id 
                            FROM 
                                uds_staff_in_team 
                            WHERE   staff_id = @staff_id
--是否只有成员拥有项目的权利
--                               and member_type = 1
                        ) 
              and based_on =2 
              and a.act_id =@RIGHTCode


    --项目角色，能查看的接点
    INSERT INTO @class
        SELECT A.team_id
            FROM
                uds_staff_in_team a,
                uds_activity b
            WHERE a.member_type = b.act_id
               and b.proc_id  = @RIGHTCode
               and a.staff_id = @staff_id

    --从角色中，能查看的接点
    INSERT INTO @class 
        SELECT team_id 
            FROM 
                UDS_Assign_Rule a
            WHERE a.role_id in (
                        SELECT role_id 
                            FROM 
                                uds_staff_in_role 
                            WHERE staff_id = @staff_id
                        )
            and based_on =3 
            and a.act_id =@RIGHTCode
    --从个人中，能查看的接点
    INSERT INTO @class 
        SELECT team_id 
            FROM 
                UDS_Assign_Rule a
            WHERE a.staff_id = @STAFF_ID
              and based_on =4 
              and a.act_id =@RIGHTCode
END


--得到间接的浏览权，即职位对其下属的项目监督权

IF EXISTS(SELECT 1 FROM @class WHERE ClassID =0) 
BEGIN
    SELECT  classid 
        FROM uds_class 
        WHERE classtype <=1 
        ORDER BY classtype 
    PRINT '全局'
    RETURN 
END
--ELSE

BEGIN
    IF @InHerit =0 
    SELECT  classid 
        FROM uds_class 
        WHERE classtype <=1 and classid in (SELECT ClassID FROM @class)
        ORDER BY classtype 
    ELSE
    BEGIN

        SET @ids =''
        SELECT @ids = @ids + CONVERT(nvarchar,classid) + ','
            FROM uds_class WHERE classid in (SELECT ClassID FROM @class)
        --IF RIGHT(@ids,1)=','
        --    SET @ids = left(@ids,len(@ids)-1)
        PRINT @ids

        CREATE TABLE #aclass (ClassID INT)
        WHILE len(@ids)>0
        BEGIN
            SET @id = convert(INT,substring(@ids,1,charindex(',',@ids)-1))

            SET @ids = substring(@ids,charindex(',',@ids)+1,len(@ids))    
            PRINT @ids
        END
        SELECT  classid 
            FROM uds_class 
            WHERE classtype <=1 and classid in (SELECT ClassID FROM #aclass)
            ORDER BY classtype         

    END

END

GO